using System;
using System.Collections.Generic;
using System.Text;
using MS.Core.Base.Data;
using LHS.Core.TableInfo.SetUpCaseRecordForm;
using System.Data;

namespace LHS.Core.CaseRecord.Classes
{
    public class clsDynamicTable
    {
        public const String PatientID = "PatientID";
        public const String OtherID1 = "OtherID1";
        public const String OtherID2 = "OtherID2";
        public const String OtherID3 = "OtherID3";
        public const String Surgical_Date = "Surgical_Date";
        public const String Surgical_Reason = "Surgical_Reason";

        public static DataTable getStructure(DataTable dtCaseRecord_Leaf, String strTableName)
        {
            DataTable dtReturn = new DataTable(strTableName);
            dtReturn.Columns.Add(PatientID, typeof(String));
            dtReturn.Columns.Add(OtherID1, typeof(String));
            dtReturn.Columns.Add(OtherID2, typeof(String));
            dtReturn.Columns.Add(OtherID3, typeof(String));
            dtReturn.Columns.Add(Surgical_Date, typeof(DateTime));
            dtReturn.Columns.Add(Surgical_Reason, typeof(String));
            foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Rows)
            {
                if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.NUMBER))
                    dtReturn.Columns.Add(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString(), typeof(Double));
                else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.OPTION))
                    dtReturn.Columns.Add(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString(), typeof(int));
                else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.YES_NO))
                    dtReturn.Columns.Add(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString(), typeof(String));
                else
                    dtReturn.Columns.Add(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString(), typeof(String));
            }
            dtReturn.Columns.Add(DBBaseInfo.Hidden_Status, typeof(int));
            return dtReturn;
        }

        public static String getSQLCreateTable(DataTable dtCaseRecord_Leaf, String strTableName)
        {
            String strReturn = "CREATE TABLE " + strTableName + "(";
            strReturn += PatientID + " VARCHAR(10), ";
            strReturn += OtherID1 + " VARCHAR(10), ";
            strReturn += OtherID2 + " VARCHAR(10), ";
            strReturn += OtherID3 + " VARCHAR(10), ";
            strReturn += Surgical_Date + " DATETIME, ";
            strReturn += Surgical_Reason + " NVARCHAR(500), ";
            foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Rows)
            {
                strReturn += drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString();
                if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.NUMBER))
                    strReturn += " FLOAT, ";
                else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.OPTION))
                    strReturn += " INT, ";
                else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.YES_NO))
                    strReturn += " CHAR(1), ";
                else
                    strReturn += " NVARCHAR(" + Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_MAXLENGTH]).ToString() + "), ";
            }
            return strReturn.Substring(0, strReturn.Length - 2) + ")";
        }

        public static String getSQLAlterTable(DataTable dtCaseRecord_Leaf, String strTableName, DataTable dtCaseRecord_Tree)
        {
            String strReturn = "ALTER TABLE " + strTableName + " ADD ";
            DataRow[] drFoundLeafArr;
            foreach (DataRow drCaseRecord_Tree in dtCaseRecord_Tree.Select(CaseRecord_TreeInfo.CRT_ISUSED + " = '" + Convert.ToInt32(clsEnumType.BooleanValue.FALES).ToString() + "'"))
            {
                drFoundLeafArr = dtCaseRecord_Leaf.Select(CaseRecord_LeafInfo.CRT_ID + " = '" + drCaseRecord_Tree[CaseRecord_TreeInfo.CRT_ID].ToString() + "'");
                if (drFoundLeafArr.Length > 0)
                {
                    strReturn += drFoundLeafArr[0][CaseRecord_LeafInfo.CRL_COLNAME].ToString();
                    if (Convert.ToInt32(drFoundLeafArr[0][CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.NUMBER))
                        strReturn += " FLOAT, ";
                    else if (Convert.ToInt32(drFoundLeafArr[0][CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.OPTION))
                        strReturn += " INT, ";
                    else if (Convert.ToInt32(drFoundLeafArr[0][CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.YES_NO))
                        strReturn += " CHAR(1), ";
                    else
                        strReturn += " NVARCHAR(" + Convert.ToInt32(drFoundLeafArr[0][CaseRecord_LeafInfo.CRL_MAXLENGTH]).ToString() + "), ";
                }
            }
            return strReturn.Substring(0, strReturn.Length - 2);
        }

        public static String getSQLCheckTableIsExists(String strTableName)
        {
            //neu la sql 2000 , su dung sysobjects  , column cua no la name
            //neu la sql 2005 , su dung sys.objects, column cua no la object_id
            return "SELECT * FROM sysobjects WHERE name = N'" + strTableName + "' AND xtype in (N'U')";
        }

        public static String getSQLDeleteTable(String strTableName)
        {
            return "DROP TABLE " + strTableName;
        }

        public static String[] getKeys()
        {
            return new String[] { PatientID, OtherID1, OtherID2, OtherID3 };
        }
    }
}
